﻿CREATE PROCEDURE [dbo].[yaf_topic_list](
                @ForumID      INT,
                @Announcement SMALLINT,
                @Date         DATETIME  = NULL,
                @Offset       INT,
                @Count        INT)
AS
    BEGIN
        CREATE TABLE #data (
          RowNo   INT IDENTITY PRIMARY KEY NOT NULL,
          TopicID INT NOT NULL)
        INSERT INTO #data
                   (TopicID)
        SELECT   c.TopicID
        FROM     yaf_Topic c
                 JOIN yaf_User b
                   ON b.UserID = c.UserID
                 JOIN yaf_Forum d
                   ON d.ForumID = c.ForumID
        WHERE    c.ForumID = @ForumID
        AND (@Date IS NULL 
              OR c.Posted >= @Date
              OR c.LastPosted >= @Date
              OR Priority > 0)
        AND ((@Announcement = 1
              AND c.Priority = 2)
              OR (@Announcement = 0
                  AND c.Priority <> 2)
              OR (@Announcement < 0))
        AND (c.TopicMovedID IS NOT NULL 
              OR c.NumPosts > 0)
        AND (c.Flags & 8) = 0
        ORDER BY Priority DESC,
                 c.LastPosted DESC
        DECLARE  @RowCount INT
        SET @RowCount = (SELECT COUNT(1)
                         FROM   #data)
        SELECT   [RowCount] = @RowCount,
                 c.ForumID,
                 c.TopicID,
                 c.Posted,
                 LinkTopicID = Isnull(c.TopicMovedID,c.TopicID),
                 c.TopicMovedID,
                 Subject = c.Topic,
                 c.UserID,
                 Starter = Isnull(c.UserName,b.Name),
                 Replies = c.NumPosts - 1,
                 Views = c.Views,
                 LastPosted = c.LastPosted,
                 LastUserID = c.LastUserID,
                 LastUserName = Isnull(c.LastUserName,(SELECT Name
                                                       FROM   yaf_User x
                                                       WHERE  x.UserID = c.LastUserID)),
                 LastMessageID = c.LastMessageID,
                 LastTopicID = c.TopicID,
                 TopicFlags = c.Flags,
                 c.Priority,
                 c.PollID,
                 ForumFlags = d.Flags
        FROM     yaf_Topic c
                 JOIN yaf_User b
                   ON b.UserID = c.UserID
                 JOIN yaf_Forum d
                   ON d.ForumID = c.ForumID
                 JOIN #data e
                   ON e.TopicID = c.TopicID
        WHERE    e.RowNo BETWEEN @Offset + 1
                        AND @Offset + @Count
        ORDER BY e.RowNo
    END


